Re: [SQL] Anyone recognise this error from PL/pgSQL?

Hi Mark,

>I'm trying my first PL/pgSQL code to trap foreign key errors. Before inserting
>into the table "Exon", I wish to be sure that a foreign key, 'zhvtID',
>exists in the table 'zhvt'. Sounds simple...

<snip code with 2 errors>

>And this is the error I get when I try to insert anything, regardless of
>whether
>the foreign key exists or not:
>
>zhvt=> insert into exon (zhvtID, number, start, stop, iscomplement)
>zhvt-> values (1, 1, 1, 100, 't');
>ERROR:  There is no operator '=$' for types 'int4' and 'int4'
>        You will either have to retype this query using an explicit cast,
>        or you will have to define the operator using CREATE OPERATOR
>

Yes, I remember a posting about this a little while ago, the solution was
so simple it made you want to kick yourself!

SELECT * INTO zhvt_row FROM zhvt         WHERE zhvtID=NEW.zhvtID;

Becomes:

SELECT * INTO zhvt_row FROM zhvt         WHERE zhvtID = NEW.zhvtID; --spaces on either side of the equal

another trick I've used in the past when getting that sort of error message
is to use the function that is used by the operator directly so:

SELECT * INTO zhvt_row FROM zhvt         WHERE texteq(zhvtID,NEW.zhvtID);

would work too.

Finally, your function needs to have a RETURN in it (even though its return
type is opaque) in case there is no problem with the INSERT/UPDATE.

So the code becomes:

CREATE FUNCTION exon_foreign_keys() RETURNS opaque AS '
DECLARE     zhvt_row zhvt%ROWTYPE;
BEGIN   IF NEW.zhvtID ISNULL THEN   RAISE EXCEPTION ''zhvtID can not be NULL'';   END IF;
   SELECT * INTO zhvt_row FROM zhvt         WHERE zhvtID = NEW.zhvtID;  -- change one   IF NOT FOUND THEN         RAISE
EXCEPTION''zhvtID= % is not in TABLE zhvt'' , NEW.zhvtID;   END IF;   RETURN new; -- change two
 
END;
' LANGUAGE 'plpgsql';

(trigger code remains the same; you'll have to drop and recreate both
function and the trigger though).

Both of these worked under PG6.4

I'm wondering if this is a bug that should be corrected in the parser or if
it is correct syntax for the operator to be bound by spaces?

Regards,

Stuart.

+--------------------------+--------------------------------------+
| Stuart C. G. Rison       | Ludwig Institute for Cancer Research |
+--------------------------+ 91 Riding House Street               |
| N.B. new phone code!!    | London, W1P 8BT                      |
| Tel. +44 (0)207 878 4041 | UNITED KINGDOM                       |
| Fax. +44 (0)207 878 4040 | stuart@ludwig.ucl.ac.uk              |
+--------------------------+--------------------------------------+